package Or

import (
	"errors"
	"fmt"
	"db2s/global"
	"strconv"
	"strings"
	"sync/atomic"
)

type TableSql struct{}

func getAtomicValue(val *atomic.Value) []map[string]string {
	if val == nil {
		return nil
	}
	rel := val.Load()
	if rel == nil {
		return nil
	}
	switch rel.(type) {
	case []map[string]string:
		return rel.([]map[string]string)
	default:
		return nil
	}
}

func (or TableSql) columnDataDis(p global.PrefixInput, s []*string) []*string {
	var news []*string
	if p.Insert.Value.IgnoreColumnLength {
		for _, v := range p.Insert.Value.ColData {
			var seq int
			if se, err := strconv.Atoi(v.ColumnSeq); err != nil {
				return nil
			} else {
				seq = se
			}
			if seq > 0 {
				news = append(news, s[seq-1])
			}
		}
	} else {
		news = s
	}
	return news
}

/*
columnValueConvert 列数据根据数据类型进行转换
*/
func columnValueConvert(TableColumn []global.TableMeta, cn, s string, logSeq int64) (d string) {
	olds := s
	if strings.Contains(s, "'") {
		s = strings.ReplaceAll(s, "'", "\\'")
	} else {
		s = fmt.Sprintf("'%s'", s)
	}
	if strings.EqualFold(cn, "rowId") {
		return s
	}
	if strings.EqualFold(olds, "NULL") {
		return olds
	}
	for _, i := range TableColumn {
		if strings.EqualFold(i.ColumnName, cn) {
			switch func(s string) (d string) {
				if s == "DATE" {
					d = "DATE"
				}
				if strings.Contains(s, "TIMESTAMP") {
					d = "TIMESTAMP"
				}
				return
			}(strings.ToUpper(i.DataType)) {
			//处理oracle查询时间列时数据带时区问题  2021-01-23 10:16:29 +0800 CST
			case "DATE":
				d = fmt.Sprintf("to_date(%s,'YYYY-MM-DD HH24:MI:SS')", s)
				//处理oracle查询时间列时数据带时区问题  2021-01-23 10:16:29 +0800 CST
			case "TIMESTAMP":
				var du string
				if strings.Contains(i.DataType, "(") && strings.Contains(i.DataType, ")") {
					l := strings.Split(i.DataType, "(")[1]
					du = strings.Split(l, ")")[0]
				}
				if len(du) > 0 {
					d = fmt.Sprintf("to_timestamp(%s,'YYYY-MM-DD HH24:MI:SS.FF%s')", s, du)
				} else {
					d = fmt.Sprintf("to_timestamp(%s,'YYYY-MM-DD HH24:MI:SS')", s)
				}
			default:
				d = s
			}
			break
		}
	}
	return
}
func rowsValueFunc(lobSwitch bool, c, v string) string {
	var tmpColumnName = fmt.Sprintf("'%v'", v)
	switch {
	case strings.EqualFold(v, "<null>"):
		if lobSwitch { //json数据的null值在lob插入是nil
			return fmt.Sprintf("<prepare nil>")
		}
		return fmt.Sprintf("NULL")
	case !strings.HasPrefix(strings.ToUpper(c), "NUMBER") && strings.EqualFold(v, "<entry>"), len(v) == 0:
		if lobSwitch {
			return fmt.Sprintf("<prepare entry>")
		}
		return fmt.Sprintf("''")
	case strings.HasPrefix(strings.ToUpper(c), "NUMBER") && strings.EqualFold(v, "<entry>"):
		return fmt.Sprintf("NULL")
	case strings.HasPrefix(strings.ToUpper(c), "DATE"):
		if len(v) == 0 {
			return fmt.Sprintf("NULL")
		}
		return fmt.Sprintf("to_date(%s,'YYYY-MM-DD HH24:MI:SS')", tmpColumnName)
	case strings.HasPrefix(strings.ToUpper(c), "RAW"):
		if len(v) == 0 {
			return fmt.Sprintf("NULL")
		}
		return fmt.Sprintf("UTL_RAW.CAST_TO_RAW(%s)", tmpColumnName)
	case strings.HasPrefix(strings.ToUpper(c), "TIMESTAMP"):
		return fmt.Sprintf("TO_TIMESTAMP(%s,'YYYY-MM-DD HH24:MI:SS.FF')", tmpColumnName)
	default:
		return tmpColumnName
	}
}

/*
LeftStitching 生成sql 语句where left 条件
*/
func LeftStitching(s global.WhereGenerateInput) (l string) {
	var (
		whereLeft []string
	)
	for _, v := range getAtomicValue(s.LeftDirection) {
		columnName, nLeftOk := v["columnName"]
		columnDataLeft, cLeftOk := v["columnDataLeft"]
		querySymbolLeft, qLeftOk := v["querySymbolLeft"]
		if cLeftOk && qLeftOk && nLeftOk {
			if strings.EqualFold(querySymbolLeft, "=") && strings.EqualFold(columnDataLeft, "NULL") { //处理等值null
				whereLeft = append(whereLeft, fmt.Sprintf("\"%s\" %s %s", columnName, " IS ", columnDataLeft))
			} else if strings.EqualFold(querySymbolLeft, ">") && strings.EqualFold(columnDataLeft, "NULL") {
				whereLeft = append(whereLeft, fmt.Sprintf("\"%s\" %s %s", columnName, " IS NOT ", columnDataLeft))
			} else {
				whereLeft = append(whereLeft, fmt.Sprintf("\"%s\" %s %s", columnName, querySymbolLeft, columnValueConvert(s.TableColumn, columnName, columnDataLeft, s.LogSeq)))
			}
		}
	}
	return strings.Join(whereLeft, " and ")
}

/*
RightStitching 生成sql 语句where right 条件
*/
func RightStitching(s global.WhereGenerateInput) string {
	var (
		whereRight []string
	)
	for _, v := range getAtomicValue(s.RightDirection) {
		columnName, nRightOk := v["columnName"]
		columnDataRight, cRightOk := v["columnDataRight"]
		querySymbolRight, qRightOk := v["querySymbolRight"]
		if cRightOk && qRightOk && nRightOk {
			if strings.EqualFold(querySymbolRight, "=") && strings.EqualFold(columnDataRight, "NULL") {
				whereRight = append(whereRight, fmt.Sprintf("\"%s\" %s %s", columnName, " IS ", columnDataRight))
			} else if strings.EqualFold(querySymbolRight, ">") && strings.EqualFold(columnDataRight, "NULL") {
				whereRight = append(whereRight, fmt.Sprintf("\"%s\" %s %s", columnName, " IS NOT ", columnDataRight))
			} else {
				whereRight = append(whereRight, fmt.Sprintf("\"%s\" %s %s", columnName, querySymbolRight, columnValueConvert(s.TableColumn, columnName, columnDataRight, s.LogSeq)))
			}
		}
	}
	return strings.Join(whereRight, " and ")
}
func SqlFilterFunc(s global.WhereGenerateInput) (w string) {
	if len(s.SqlFilter.WhereSql) > 0 {
		return s.SqlFilter.WhereSql
	}
	return
}
func (or TableSql) OrderBY(s global.WhereGenerateInput) (res string, err error) {
	var orderByColumn []string
	if strings.EqualFold(s.NullConstraint, "null") {
		return
	}
	var uniqOrderByColumn = make(map[string]int)
	for _, v := range s.IndexColumn {
		for _, v1 := range getAtomicValue(s.LeftDirection) {
			if v2, ok := v1["columnName"]; ok {
				if strings.EqualFold(v, v2) {
					if _, ok2 := uniqOrderByColumn[v]; !ok2 {
						uniqOrderByColumn[v]++
						orderByColumn = append(orderByColumn, v)
					}
				}
			}
		}
	}
	if len(orderByColumn) > 0 {
		return fmt.Sprintf("order by \"%v\"", strings.Join(orderByColumn, "\",\"")), nil
	}
	return
}

/*
WhereGenerate 生成sql语句的where条件
*/
func (or TableSql) WhereGenerate(s global.WhereGenerateInput) (ConditionSlice []string, err error) {
	if len(s.SqlFilter.WhereSql) == 0 {
		if v := LeftStitching(s); len(v) > 0 {
			ConditionSlice = append(ConditionSlice, v)
		}
		if v := RightStitching(s); len(v) > 0 {
			ConditionSlice = append(ConditionSlice, v)
		}
	} else {
		if v := SqlFilterFunc(s); len(v) > 0 {
			ConditionSlice = append(ConditionSlice, v)
		}
	}
	return
}
func columnMetaGroup(s global.SelectColumnInput) (allTableColumn []global.TableMeta) {
	var (
		readObject []string
	)
	switch s.OutputColumnType {
	case "all":
		allTableColumn = s.TableColumn
	case "specify":
		readObject = s.SelectColumn
	default:
		readObject = s.IndexColumn
	}
	for _, v1 := range readObject {
		for _, vv := range s.TableColumn {
			if strings.EqualFold(vv.ColumnName, v1) {
				allTableColumn = append(allTableColumn, vv)
				break
			}
		}
	}
	return
}
func timeTypeToFunc(i global.TableMeta, columnName, asColumn string) (r string) {
	switch i.ConvertColumnType.Format {
	case "year-month-day hour:minute:second":
		r = fmt.Sprintf("to_char(%s,'YYYY-MM-DD HH24:MI:SS') as \"%v\"", columnName, asColumn)
	case "year-month-day hour:minute:second.utc":
		r = fmt.Sprintf("to_char(%s,'YYYY-MM-DD HH24:MI:SS TZR') as \"%v\"", columnName, asColumn)
	case "year-month-day hour:minute:second.milli(1)":
		r = fmt.Sprintf("to_char(%s,'YYYY-MM-DD HH24:MI:SS.FF%d') as \"%v\"", columnName, 1, asColumn)
	case "year-month-day hour:minute:second.milli(1).utc":
		r = fmt.Sprintf("to_char(%s,'YYYY-MM-DD HH24:MI:SS.FF%d TZR') as \"%v\"", columnName, 1, asColumn)
	case "year-month-day hour:minute:second.milli(2)":
		r = fmt.Sprintf("to_char(%s,'YYYY-MM-DD HH24:MI:SS.FF%d') as \"%v\"", columnName, 2, asColumn)
	case "year-month-day hour:minute:second.milli(2).utc":
		r = fmt.Sprintf("to_char(%s,'YYYY-MM-DD HH24:MI:SS.FF%d TZR') as \"%v\"", columnName, 2, asColumn)
	case "year-month-day hour:minute:second.milli(3)":
		r = fmt.Sprintf("to_char(%s,'YYYY-MM-DD HH24:MI:SS.FF%d') as \"%v\"", columnName, 3, asColumn)
	case "year-month-day hour:minute:second.milli(3).utc":
		r = fmt.Sprintf("to_char(%s,'YYYY-MM-DD HH24:MI:SS.FF%d TZR') as \"%v\"", columnName, 3, asColumn)
	case "year-month-day hour:minute:second.micro(1)":
		r = fmt.Sprintf("to_char(%s,'YYYY-MM-DD HH24:MI:SS.FF%d') as \"%v\"", columnName, 4, asColumn)
	case "year-month-day hour:minute:second.micro(1).utc":
		r = fmt.Sprintf("to_char(%s,'YYYY-MM-DD HH24:MI:SS.FF%d TZR') as \"%v\"", columnName, 4, asColumn)
	case "year-month-day hour:minute:second.micro(2)":
		r = fmt.Sprintf("to_char(%s,'YYYY-MM-DD HH24:MI:SS.FF%d') as \"%v\"", columnName, 5, asColumn)
	case "year-month-day hour:minute:second.micro(2).utc":
		r = fmt.Sprintf("to_char(%s,'YYYY-MM-DD HH24:MI:SS.FF%d TZR') as \"%v\"", columnName, 5, asColumn)
	case "year-month-day hour:minute:second.micro(3)":
		r = fmt.Sprintf("to_char(%s,'YYYY-MM-DD HH24:MI:SS.FF%d') as \"%v\"", columnName, 6, asColumn)
	case "year-month-day hour:minute:second.micro(3).utc":
		r = fmt.Sprintf("to_char(%s,'YYYY-MM-DD HH24:MI:SS.FF%d TZR') as \"%v\"", columnName, 6, asColumn)
	case "year-month-day hour:minute:second.nano(1)":
		r = fmt.Sprintf("to_char(%s,'YYYY-MM-DD HH24:MI:SS.FF%d') as \"%v\"", columnName, 7, asColumn)
	case "year-month-day hour:minute:second.nano(1).utc":
		r = fmt.Sprintf("to_char(%s,'YYYY-MM-DD HH24:MI:SS.FF%d TZR') as \"%v\"", columnName, 7, asColumn)
	case "year-month-day hour:minute:second.nano(2)":
		r = fmt.Sprintf("to_char(%s,'YYYY-MM-DD HH24:MI:SS.FF%d') as \"%v\"", columnName, 8, asColumn)
	case "year-month-day hour:minute:second.nano(2).utc":
		r = fmt.Sprintf("to_char(%s,'YYYY-MM-DD HH24:MI:SS.FF%d TZR') as \"%v\"", columnName, 8, asColumn)
	case "year-month-day hour:minute:second.nano(3)":
		r = fmt.Sprintf("to_char(%s,'YYYY-MM-DD HH24:MI:SS.FF%d') as \"%v\"", columnName, 9, asColumn)
	case "year-month-day hour:minute:second.nano(3).utc":
		r = fmt.Sprintf("to_char(%s,'YYYY-MM-DD HH24:MI:SS.FF%d TZR') as \"%v\"", columnName, 9, asColumn)
	default:
		r = fmt.Sprintf("to_char(%s,'YYYY-MM-DD HH24:MI:SS') as \"%v\"", columnName, asColumn)
	}
	return
}
func fmToString(n int, p string) string {
	var s []string
	if n > 38 {
		n = 38
	}
	for i := 0; i < n; i++ {
		s = append(s, p)
	}
	return strings.Join(s, "")
}
func stringToInt(s string) int {
	i, err := strconv.Atoi(s)
	if err != nil {
		return 0
	}
	return i
}
func decimalTypeToFunc(i global.TableMeta, columnName, asColumn string) (r string) {
	switch i.ConvertColumnType.Type {
	case "decimal":
		switch i.ConvertColumnType.Format {
		case "Precision", "Precision,(-Scale)":
			r = fmt.Sprintf("to_char(%s,'FM%s0') as \"%v\" ", columnName, fmToString(i.ConvertColumnType.ShowSize.(int), "9"), asColumn)
		case "Precision,Scale":
			p := strings.Split(fmt.Sprintf("%v", i.ConvertColumnType.ShowSize), ",")
			var precision, scale = stringToInt(p[0]), stringToInt(p[1])
			r = fmt.Sprintf("to_char(%s,'FM%s0.%s') as \"%v\" ", columnName, fmToString(precision-scale, "9"), fmToString(scale, "0"), asColumn)
		case "(max,max)":
			r = fmt.Sprintf("to_char(%s) as \"%v\" ", columnName, asColumn)
		case "Scale,Precision":
			p := strings.Split(fmt.Sprintf("%v", i.ConvertColumnType.ShowSize), ",")
			r = fmt.Sprintf("to_char(%s,'FM0.%s') as \"%v\" ", columnName, fmToString(stringToInt(p[1]), "0"), asColumn)
		default:
			r = fmt.Sprintf("%v as \"%v\"", columnName, asColumn)
		}
	default:
		r = fmt.Sprintf("%v as \"%v\"", columnName, asColumn)
	}
	return
}
func specialTypeToFunc(i global.TableMeta, columnName string) (r string) {
	asColumn := fmt.Sprintf("C%v", i.ColumnSeq)
	switch i.ConvertColumnType.Type {
	case "time":
		return timeTypeToFunc(i, columnName, asColumn)
	case "decimal":
		return decimalTypeToFunc(i, columnName, asColumn)
	case "char":
		return fmt.Sprintf("rtrim(%s) as \"%v\"", columnName, asColumn)
	default:

		return fmt.Sprintf("%v as \"%v\"", columnName, asColumn)
	}
}
func columnNameActive(s global.SelectColumnInput, i global.TableMeta) (r string) {
	r = colNameUnLock(s, i)
	return
}
func colNameUnLock(s global.SelectColumnInput, i global.TableMeta) (r string) {
	r = fmt.Sprintf("\"%s\"", i.ColumnName)
	for _, v := range s.Esa.ColumnSlice {
		if strings.EqualFold(v, i.ColumnName) {
			r = fmt.Sprintf("%v.%v(%v) ", s.Schema, s.Esa.UnLockFuncName, r)
			break
		}
	}
	return
}
func colNameLock(esaCol []string, schema string, lockFunc string, columnName string) (r string) {
	r = fmt.Sprintf("\"%s\"", columnName)
	for _, v := range esaCol {
		if strings.EqualFold(v, columnName) && len(lockFunc) > 0 {
			r = fmt.Sprintf("%v.%v(%v) ", schema, lockFunc, r)
			break
		}
	}
	return
}
func (or TableSql) SelectColumn(s global.SelectColumnInput) (columnName, asColumnName []string, err error) {
	for _, i := range columnMetaGroup(s) {
		asColumnName = append(asColumnName, fmt.Sprintf("C%v", i.ColumnSeq))
		columnName = append(columnName, specialTypeToFunc(i, columnNameActive(s, i)))
	}
	return
}
func (or TableSql) Prefix(s global.PrefixInput) (insertPrefix string, err error) {
	var (
		tableName string
	)
	if !strings.EqualFold(s.Insert.Prefix.TableInfo.PartitionName, "single") && len(s.Insert.Prefix.TableInfo.PartitionName) > 0 {
		tableName = fmt.Sprintf(" \"%v\".\"%v\" PARTITION (\"%v\")", s.Insert.Prefix.TableInfo.Schema, s.Insert.Prefix.TableInfo.Table, s.Insert.Prefix.TableInfo.PartitionName)
	} else {
		tableName = fmt.Sprintf(" \"%v\".\"%v\" ", s.Insert.Prefix.TableInfo.Schema, s.Insert.Prefix.TableInfo.Table)
	}
	insertPrefix = fmt.Sprintf("%s ", fmt.Sprintf("INSERT INTO %v (\"%s\") VALUES", tableName, strings.Join(s.Insert.Prefix.InsertColumn, "\",\"")))
	if s.Insert.Prefix.LobSwitch {
		insertPrefix = fmt.Sprintf("INSERT INTO %v (\"%s\") VALUES (%s)", tableName, strings.Join(s.Insert.Prefix.InsertColumn, "\",\""), strings.Join(s.Insert.Prefix.PlaceholderValue, ","))
	}
	return
}

func colNameMatchRows(p global.PrefixInput, s []*string) (res []*string, err error) {
	var news []*string
	res = s
	if !p.Insert.Value.IgnoreColumnLength {
		if len(p.Insert.Value.ColData) != len(s) {
			err = errors.New(fmt.Sprintf("column length not match"))
		}
		return
	}
	for _, v := range p.Insert.Value.ColData {
		var seq int
		if seq, err = strconv.Atoi(v.ColumnSeq); err != nil {
			return
		}
		if seq > 0 {
			news = append(news, s[seq-1])
		}
	}
	res = news
	return
}
func specialCharacterEscape(s *string) (r string) {
	var l = *s
	if strings.Contains(l, "\\") {
		l = strings.ReplaceAll(l, "\\", "\\\\")
	}
	if strings.Contains(l, "'") {
		l = strings.ReplaceAll(l, "'", "\\'")
	}
	if strings.Contains(l, ";") {
		l = strings.ReplaceAll(l, ";", "\\;")
	}
	return
}

/*
Insert 生成insert语句前缀
*/
func (or TableSql) Insert(s global.PrefixInput) (res any, err error) {
	//查询该表的列名和列信息
	var (
		sqlResult []*string
	)
	var manyRowsValue []string
	var lobManyRowsValue [][]*string
	for _, rows := range s.Insert.Value.RowData {
		if len(rows) == 0 {
			continue
		}
		if rows, err = colNameMatchRows(s, rows); err != nil {
			return
		}
		var singleRowsValue []string
		var lobSingleRowsValue []*string
		for k, v := range rows {
			columnVal := colNameLock(s.Insert.Value.Esa.ColumnSlice, s.Insert.Prefix.TableInfo.Schema, s.Insert.Value.Esa.LockFuncName, specialTypeInsertActive(s.Insert.Prefix.LobSwitch, specialCharacterEscape(v), s.Insert.Value.ColData[k]))
			singleRowsValue = append(singleRowsValue, columnVal)
			lobSingleRowsValue = append(lobSingleRowsValue, &columnVal)
		}
		manyRowsValue = append(manyRowsValue, strings.Join(singleRowsValue, ","))
		lobManyRowsValue = append(lobManyRowsValue, lobSingleRowsValue)
	}
	if strings.Contains(s.Insert.Value.Prefix, "(?") {
		return lobManyRowsValue, nil
	}
	if len(manyRowsValue) == 0 {
		return
	}
	for _, v := range manyRowsValue {
		q := fmt.Sprintf("%v (%v)", s.Insert.Value.Prefix, v)
		sqlResult = append(sqlResult, &q)
	}
	return sqlResult, nil
}
func (or TableSql) Delete(s global.PrefixInput) (any, error) {
	var (
		deleteGather   []*string
		deleteSql      string
		whereColumnMap = make(map[int] /*列序号+ 列名字*/ string)
	)
	for i, v := range s.Delete.Value.ColData {
		switch s.Delete.Value.IndexType {
		case "pri", "uni":
			for m, n := range s.Delete.Value.IndexColumnSeq {
				if strings.EqualFold(fmt.Sprintf("%v", i+1), m) {
					//获取列名和列类型
					whereColumnMap[i] = n
				}
			}
		case "mul":
			//获取列名和列类型
			whereColumnMap[i] = v.ColumnName
		}
	}
	for _, v := range s.Delete.Value.RowData {
		var o []string
		for p, l := range v {
			if c, ok := whereColumnMap[p]; ok {
				o = append(o, fmt.Sprintf(" %v = '%v'", c, specialCharacterEscape(l)))
			}
		}
		if len(o) > 0 {
			deleteSql = fmt.Sprintf("delete from \"%s\".\"%s\" where %s;", s.Delete.Value.TableInfo.Schema, s.Delete.Value.TableInfo.Table, strings.Join(o, " and "))
			deleteGather = append(deleteGather, &deleteSql)
		}
	}
	return deleteGather, nil
}
